-- **************************************************************************
--    Project Name:   诊断分析-业务量指标分析客户汇总表
--    Job Name:       jms_dm.dm_cust_target_analysis_sum_day_dt
--    Author :        季修魁
--    date：          2023/11/16
-- **************************************************************************
-- **************************************************************************

with tab_order_cnt_agg as (
select  to_date(input_time)                                       as stat_date                      
        ,order_source_code                                        as order_source_code
        ,order_source_name                                        as order_source_name
        ,pick_network_code                                        as pick_network_code 
        ,pick_network_name                                        as pick_network_name
		,pick_staff_code                                          as staff_code
        ,pick_staff_name                                          as staff_name
        ,customer_code		                                      as customer_code
		,customer_name                                            as customer_name
	    ,count(1)                                                 as order_cnt                        ---订单量
	    ,sum(case when order_status_code = 104 then 1 else 0 end) as cancle_cnt                       ---取消量
	    ,sum(case when order_status_code = 101 then 1 else 0 end) as notcollect_cnt                   ---未揽收量		
   from jms_dwd.dwd_yl_oms_oms_order_incre_dt    a 
   left join jms_dim.dim_network_whole_massage   b 
     on a.pick_network_code=b.code
  where dt>=date_add('{{ execution_date | cst_ds }}',-7) and dt <='{{ execution_date | cst_ds }}'
    and nvl(b.agent_code,a.proxy_area_code) <> 'Test001'
    and a.pick_network_code <> '888888'
    and instr(upper(a.pick_network_code), 'TEST') = 0
    and instr(upper(a.pick_network_code), 'B') = 0  
  group by to_date(input_time)
           ,order_source_code  
           ,order_source_name  
           ,pick_network_code  
           ,pick_network_name    
           ,pick_staff_code      
           ,pick_staff_name      
           ,customer_code	
           ,customer_name
),

----运单指标

 tab_waybill_cnt_agg as (
select to_date(input_time)                                       as stat_date
        ,a.pick_network_code                                     as pick_network_code
        ,a.pick_network_name                                     as pick_network_name
        ,a.ordersource_code                                      as order_source_code
        ,nvl(a.ordersource_name,'其他')                          as order_source_name
        ,a.collect_staff_code                                    as staff_code   ---揽收业务员
        ,a.collect_staff_name                                    as staff_name   
        ,a.customer_code                                         as customer_code       
        ,a.customer_name                                         as customer_name
        ,count(1)                                                                                 as total_cnt                       ---业务量（运单）
        ,sum(case when nvl(is_void,0) = 1 then 1 else 0 end)                                      as void_cnt                        ---作废量
 from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt  a 
 left join jms_dim.dim_network_whole_massage   b   
   on a.pick_network_code=b.code 
where dt>=date_add('{{ execution_date | cst_ds }}',-31) and dt <='{{ execution_date | cst_ds }}'
  and to_date(input_time)>=date_add('{{ execution_date | cst_ds }}',-7)  and to_date(input_time) <='{{ execution_date | cst_ds }}'
  and nvl(b.agent_code, a.pick_finance_code) not in( 'Test001','Test0001')
  and a.pick_network_code <> '888888'
  and instr(upper(a.pick_network_code), 'TEST') = 0
  and instr(upper(a.pick_network_code), 'B') = 0
group by to_date(input_time) 
        ,a.pick_network_code                           
        ,a.pick_network_name                           
        ,a.ordersource_code                            
        ,nvl(a.ordersource_name,'其他')                
        ,a.collect_staff_code                          
        ,a.collect_staff_name                          
        ,a.customer_code                               
        ,a.customer_name                               
),

tab_order_way_merge as (
select stat_date
	   ,order_source_code
	   ,order_source_name
       ,pick_network_code
	   ,pick_network_name
       ,staff_code
       ,staff_name
       ,customer_code
	   ,customer_name
	   ,sum(order_cnt)                      order_cnt                   
	   ,sum(cancle_cnt)                     cancle_cnt                  
	   ,sum(notcollect_cnt)                 notcollect_cnt              
	   ,sum(total_cnt)                      total_cnt                   
	   ,sum(void_cnt)                       void_cnt                     
  from 
( 
select stat_date
	   ,order_source_code
	   ,order_source_name
       ,pick_network_code
	   ,pick_network_name
       ,staff_code
       ,staff_name
       ,customer_code
	   ,customer_name
	   ,sum(order_cnt)       as order_cnt                     ---订单量
	   ,sum(cancle_cnt)      as cancle_cnt                    ---取消量
	   ,sum(notcollect_cnt)  as notcollect_cnt                ---未揽收量
	   ,0                    as total_cnt                     ---业务量（运单）
	   ,0                    as void_cnt                      ---作废量
  from tab_order_cnt_agg
 group by stat_date,order_source_code,order_source_name,pick_network_code,pick_network_name,staff_code,staff_name,customer_code,customer_name	  
union all   
select stat_date
       ,order_source_code
       ,order_source_name
       ,pick_network_code
       ,pick_network_name
       ,staff_code
       ,staff_name
       ,customer_code
       ,customer_name
       ,0                    as order_cnt                     ---订单量
       ,0                    as cancle_cnt                    ---取消量
       ,0                    as notcollect_cnt                ---未揽收量
       ,sum(total_cnt)                                       as total_cnt                     ---业务量（运单）
       ,sum(void_cnt)                                        as void_cnt                      ---作废量
  from tab_waybill_cnt_agg
 group by stat_date,order_source_code,order_source_name,pick_network_code,pick_network_name,staff_code,staff_name,customer_code,customer_name 
) t
group by stat_date,order_source_code,order_source_name,pick_network_code,pick_network_name,staff_code,staff_name,customer_code,customer_name 
)

insert overwrite table jms_dm.dm_cust_target_analysis_sum_day_dt partition(dt)
select res.stat_Date                as stat_Date
       ,cust.id                     as cust_id 
       ,res.customer_code           as cust_code
	   ,cust.name                   as cust_name
       ,cust_add.customer_level     as cust_level
	   ,res.order_source_code       as ordersource_code
	   ,res.order_source_name       as ordersource_name
	   ,cust_add.customer_category  as cust_category
	   ,nvl(cust.type_id,38)        as cust_type
	   ,cust.project_name           as cust_project_name
	   ,loss.is_loss                as cust_is_loss	
	   ,loss.is_active              as cust_is_active	
	   ,loss.is_silent              as cust_is_silent	
	   ,loss.is_incr                as cust_is_incr	
       ,res.staff_code	            as staff_code
       ,res.staff_name	            as staff_name
       ,res.pick_network_code       as network_code                   
       ,net.name                    as network_name
       ,net.fran_code               as fran_code
       ,net.fran_name               as fran_name
	   ,net.agent_code              as agent_code 
	   ,net.agent_name              as agent_name
       ,res.order_cnt               as order_cnt        
	   ,res.cancle_cnt              as cancle_cnt     
       ,res.notcollect_cnt          as notcollect_cnt 
       ,res.total_cnt               as total_cnt      
       ,res.void_cnt                as void_cnt       
       ,stat_date                   as dt
  from tab_order_way_merge   res 
  left join jms_dim.dim_network_whole_massage       net       ----网点资料表
    on res.pick_network_code=net.code                             
  left join jms_dim.dim_lmdm_sys_customer           cust      ----客户资料表	
    on res.customer_code=cust.code  
  left join jms_dim.dim_lmdm_sys_customer_addition  cust_add  ----客户资料扩展表
    on res.customer_code=cust_add.code
  left join (select customer_code,network_code,dt,max(is_loss) as is_loss,max(is_active) as is_active,max(is_silent) as is_silent,max(is_incr) as is_incr
               from jms_dm.dm_network_customer_loss_detail_dt 
			  where dt>=date_add('{{ execution_date | cst_ds }}',-7) and dt <='{{ execution_date | cst_ds }}' 
			  group by customer_code,network_code,dt
			 )  loss       ----客户流失明细
    on res.customer_code=loss.customer_code
   and res.pick_network_code=loss.network_code
   and res.stat_Date = loss.dt
 distribute by dt,pmod(hash(rand()), 10);
